import os
import pandas as pd

data = pd.read_excel(os.getcwd() + "\\data\\1647848272130494.xlsx", header=0)
df = pd.read_excel(os.getcwd() + "\\data\\predict_task3.xlsx", header=0)
clientAvgData = pd.read_csv(os.getcwd() + "\\data\\居民客户的用电缴费习惯分析1.csv", header=0)
clientAvgData.insert(loc=3, column='未来12个月平均缴费金额(元)', value=0)
start = clientAvgData['用户编号'][0]
end = clientAvgData['用户编号'][clientAvgData.size / 4 - 1] + 1
for i in range(start, end, 1):
    clientAvgData.loc[clientAvgData['用户编号'] == i, ['未来12个月平均缴费金额(元)']] = df['客户' + str(i)].sum() / 12
res = clientAvgData.sort_values(by='未来12个月平均缴费金额(元)', ascending=False)
avgCount = data["用户编号"].count() / 100
show = res[res['平均缴费次数(年)'] > avgCount].head()
print(show)
show.to_csv(os.getcwd() + "\\data\\居民客户的用电缴费习惯分析3.csv")
